CREATE VIEW [dbo].[vOppMemberContact]
AS
SELECT     OM.OpportunityKey, OM.Id AS OpportunityId, OM.Description AS OpportunityName, OM.OpportunityStatusCode AS OpportunityStatus,
                      OM.CurrentActionPlanStageName AS CurrentStage, OMM.DecisionDate, OMM.Subscription, OMM.ResponseMedia, OMM.TimingProbability,
                      OM.WinProbability, OMM.Actual, OMM.Potential, OMM.Quality, OMM.Units, N.ID AS ProspectId, N.FULL_NAME AS ProspectName,
                      N.FULL_ADDRESS AS ProspectAddress, N.MEMBER_TYPE AS ProspectMemberType, N.STATUS AS ProspectStatus,
                      N.LAST_FIRST AS ProspectLastFirst, N.TITLE AS ProspectTitle, N.PREFIX AS ProspectPrefix, N.SUFFIX AS ProspectSuffix,
                      N.DESIGNATION AS ProspectDesignation, N.INFORMAL AS ProspectInformal, N.WORK_PHONE AS ProspectWorkPhone,
                      N.HOME_PHONE AS ProspectHomePhone, N.FAX AS ProspectFax, N.TOLL_FREE AS ProspectTollFree, N.EMAIL AS ProspectEmail,
                      N.CITY AS ProspectCity, N.STATE_PROVINCE AS ProspectStateProvince, N.ZIP AS ProspectZip, N.COUNTRY AS ProspectCountry,
                      N.FUNCTIONAL_TITLE AS ProspectFunctionalTitle, N.CONTACT_RANK AS ProspectContactRank, OGRR.GroupRoleName AS GroupName,
                      GTR.GroupTypeName AS GroupType, OWN.ID AS Id, OWN.FULL_NAME AS Name, OWN.FULL_ADDRESS AS Address,
                      OWN.MEMBER_TYPE AS MemberType, OWN.STATUS AS Status, OWN.LAST_FIRST AS LastFirst, OWN.TITLE AS Title, OWN.PREFIX AS Prefix,
                      OWN.SUFFIX AS Suffix, OWN.DESIGNATION AS Designation, OWN.INFORMAL AS Informal, OWN.WORK_PHONE AS WorkPhone,
                      OWN.HOME_PHONE AS HomePhone, OWN.FAX AS Fax, OWN.TOLL_FREE AS TollFree, OWN.EMAIL AS Email, OWN.CITY AS City,
                      OWN.STATE_PROVINCE AS StateProvince, OWN.ZIP AS Zip, OWN.COUNTRY AS Country, OWN.FUNCTIONAL_TITLE AS FunctionalTitle,
                      OWN.CONTACT_RANK AS ContactRank
FROM         dbo.OpportunityMain AS OM INNER JOIN
                      dbo.OpportunityMainMember AS OMM ON OM.OpportunityKey = OMM.OpportunityKey LEFT OUTER JOIN
                      dbo.ContactMain AS UM ON OM.ProspectKey = UM.ContactKey LEFT OUTER JOIN
                      dbo.Name AS N ON UM.SyncContactID = N.ID LEFT OUTER JOIN
                      dbo.GroupMain AS OG ON OM.OpportunityOwnerGroupKey = OG.GroupKey LEFT OUTER JOIN
                      dbo.GroupMember AS OGM ON OG.GroupKey = OGM.GroupKey LEFT OUTER JOIN
                      dbo.GroupMemberDetail AS OGMD ON OGMD.GroupMemberKey = OGM.GroupMemberKey LEFT OUTER JOIN
                      dbo.GroupRoleRef AS OGRR ON OGRR.GroupRoleKey = OGMD.GroupRoleKey LEFT OUTER JOIN
                      dbo.ContactMain AS OUM ON OGM.MemberContactKey = OUM.ContactKey LEFT OUTER JOIN
                      dbo.Name AS OWN ON OUM.SyncContactID = OWN.ID INNER JOIN
                      dbo.GroupTypeRef AS GTR ON OG.GroupTypeKey = GTR.GroupTypeKey
WHERE     (OWN.ID IS NOT NULL)
UNION
SELECT     OM.OpportunityKey, OM.Id AS OpportunityId, OM.Description AS OpportunityName, OM.OpportunityStatusCode AS OpportunityStatus,
                      OM.CurrentActionPlanStageName AS CurrentStage, OMM.DecisionDate, OMM.Subscription, OMM.ResponseMedia, OMM.TimingProbability,
                      OM.WinProbability, OMM.Actual, OMM.Potential, OMM.Quality, OMM.Units, N.ID AS ProspectId, N.FULL_NAME AS ProspectName,
                      N.FULL_ADDRESS AS ProspectAddress, N.MEMBER_TYPE AS ProspectMemberType, N.STATUS AS ProspectStatus,
                      N.LAST_FIRST AS ProspectLastFirst, N.TITLE AS ProspectTitle, N.PREFIX AS ProspectPrefix, N.SUFFIX AS ProspectSuffix,
                      N.DESIGNATION AS ProspectDesignation, N.INFORMAL AS ProspectInformal, N.WORK_PHONE AS ProspectWorkPhone,
                      N.HOME_PHONE AS ProspectHomePhone, N.FAX AS ProspectFax, N.TOLL_FREE AS ProspectTollFree, N.EMAIL AS ProspectEmail,
                      N.CITY AS ProspectCity, N.STATE_PROVINCE AS ProspectStateProvince, N.ZIP AS ProspectZip, N.COUNTRY AS ProspectCountry,
                      N.FUNCTIONAL_TITLE AS ProspectFunctionalTitle, N.CONTACT_RANK AS ProspectContactRank, CGRR.GroupRoleName AS GroupName,
                      GTR.GroupTypeName AS GroupType, CN.ID AS Id, CN.FULL_NAME AS Name, CN.FULL_ADDRESS AS Address, CN.MEMBER_TYPE AS MemberType,
                      CN.STATUS AS Status, CN.LAST_FIRST AS LastFirst, CN.TITLE AS Title, CN.PREFIX AS Prefix, CN.SUFFIX AS Suffix, CN.DESIGNATION AS Designation,
                      CN.INFORMAL AS Informal, CN.WORK_PHONE AS WorkPhone, CN.HOME_PHONE AS HomePhone, CN.FAX AS Fax, CN.TOLL_FREE AS TollFree,
                      CN.EMAIL AS Email, CN.CITY AS City, CN.STATE_PROVINCE AS StateProvince, CN.ZIP AS Zip, CN.COUNTRY AS Country,
                      CN.FUNCTIONAL_TITLE AS FunctionalTitle, CN.CONTACT_RANK AS ContactRank
FROM         dbo.OpportunityMain AS OM INNER JOIN
                      dbo.OpportunityMainMember AS OMM ON OM.OpportunityKey = OMM.OpportunityKey LEFT OUTER JOIN
                      dbo.ContactMain AS UM ON OM.ProspectKey = UM.ContactKey LEFT OUTER JOIN
                      dbo.Name AS N ON UM.SyncContactID = N.ID LEFT OUTER JOIN
                      dbo.GroupMain AS CG ON OM.OpportunityContactGroupKey = CG.GroupKey LEFT OUTER JOIN
                      dbo.GroupMember AS CGM ON CG.GroupKey = CGM.GroupKey LEFT OUTER JOIN
                      dbo.GroupMemberDetail AS CGMD ON CGMD.GroupMemberKey = CGM.GroupMemberKey LEFT OUTER JOIN
                      dbo.GroupRoleRef AS CGRR ON CGRR.GroupRoleKey = CGMD.GroupRoleKey LEFT OUTER JOIN
                      dbo.ContactMain AS CUM ON CGM.MemberContactKey = CUM.ContactKey LEFT OUTER JOIN
                      dbo.Name AS CN ON CUM.SyncContactID = CN.ID INNER JOIN
                      dbo.GroupTypeRef AS GTR ON CG.GroupTypeKey = GTR.GroupTypeKey
WHERE     (CN.ID IS NOT NULL)

GO